﻿// Copyright (c) 2006 Antu Corporation。 
// 创建人  ：LinJian
// 创建时间：2007年5月23日
using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Collections;
using Antu.DataAccess;
using Antu.Business.Struct;

namespace Antu.Business.WorkFlow
{
    /// <!--
    /// 功能描述  : 根据用户获取工作流信息
    /// 创建人  : LinJian
    /// 创建时间: 2007年5月23日
    /// -->
    public class ClsUserWorkFlow
    {
        /// <summary>
        /// 根据用户id获取所能受理的工作流
        /// </summary>
        /// <param name="strUserId">用户id</param>
        /// <returns></returns>
        public DataTable GetWorkFlowByUserId(string strUserId)
        {
            string strSql = string.Format("select m.* from st_workflow m, st_step n where m.WID in "+
                "(select WID from st_group_in_step where GID in (select GID from st_user_group t "+
                "where t.userid = '{0}')) and m.WID = n.Wid and n.step_type = 0 and m.rot = 0",
                strUserId);

            DataTable dtReturn;
            SysParams.OAConnection().RunSql(strSql, out dtReturn);

            return dtReturn;
        }

        /// <summary>
        /// 判断此工作流是否存在
        /// </summary>
        /// <param name="strFlowName">工作流名称</param>
        /// <param name="strFlowType">工作流类型</param>
        /// <returns></returns>
        public string IsHaveWorkFlow(string strFlowName,string strFlowType)
        {
            string strSql = string.Format("select FLOWNAME from xt_workflow_define where flowname='{0}'"+
                " and flowtype='{1}'", strFlowName, strFlowType);
            return SysParams.OAConnection().GetValue(strSql);
        }

        /// <summary>
        /// 根据工作流类型获取工作流信息
        /// </summary>
        /// <param name="strFlowType"></param>
        /// <returns></returns>
        public DataTable GetWorkFlowByType(string strFlowType)
        {
            string strSql = string.Format("select FLOWNAME from xt_workflow_define where"+
                " flowtype='{0}'", strFlowType);

            DataTable dtOut;
            SysParams.OAConnection().RunSql(strSql,out dtOut);

            return dtOut;
        }

        /// <summary>
        /// 根据id更新指定业务的优先级别
        /// </summary>
        /// <param name="strSerialId">业务id</param>
        /// <param name="strSerialStatus">业务休闲级</param>
        /// <returns></returns>
        public int UpdateInstancePriority(string strSerialId, string strSerialStatus)
        {
            string strSql = string.Format("update st_instance set priority='{0}'" +
                " where iid='{1}'", strSerialStatus, strSerialId);
            return SysParams.OAConnection().RunSql(strSql);
        }

        /// <summary>
        /// 获取工作流的名称
        /// </summary>
        /// <returns></returns>
        public DataTable GetFlowName()
        {
            string strSql = "select distinct wname from st_workflow";
            DataTable dtOut;
            SysParams.OAConnection().RunSql(strSql, out dtOut);

            return dtOut;
        }

        /// <summary>
        /// 根据id获取工作流名称
        /// </summary>
        /// <param name="strWid"></param>
        /// <returns></returns>
        public string GetFlowName(string strWid)
        {
            string strSql = string.Format("select wname from st_workflow where wid='{0}'",
                strWid);

            return SysParams.OAConnection().GetValue(strSql);
        }

        /// <summary>
        /// 根据流程名称获取最高版本工作流id
        /// </summary>
        /// <param name="strName">工作流名称</param>
        /// <returns></returns>
        public string GetFlowIdByName(string strName)
        {
            string strSql = string.Format("select wid from st_workflow where wname='{0}' "+
                "and rownum=1 order by version desc", strName);

            return SysParams.OAConnection().GetValue(strSql);
        }

        /// <summary>
        /// 查找正在办件的人
        /// </summary>
        /// <param name="strIid"></param>
        /// <returns></returns>
        public string[] FindSerialOnPeople(string strIid)
        {
            string strSql = string.Format("select step, userid from st_work_item where wiid in"+
                " (select max(wiid) from st_work_item where iid='{0}')", strIid);

            DataTable dtOut;
            SysParams.OAConnection().RunSql(strSql, out dtOut);

            string[] arrStrReturn = new string[2];

            if (dtOut.Rows.Count > 0)
            {
                arrStrReturn[0] = dtOut.Rows[0][0].ToString();
                arrStrReturn[1] = dtOut.Rows[0][1].ToString();
            }

            return arrStrReturn;
        }

        /// <summary>
        /// 新建业务流程
        /// </summary>
        /// <param name="workFlowInfo">新建流程所需要的信息</param>
        public void NewInstance(WorkFlowInstanceStruct workFlowInfo)
        {
            IDataAccess dataAccess = SysParams.OAConnection(true);

            try
            {
                string strSql = string.Format("insert into xt_instance_ext(iid,申请单位,联系人,"+
                    "联系人电话) values({0},'{1}','{2}','{3}')", workFlowInfo.InstanceId,
                    workFlowInfo.RequestUnit,workFlowInfo.PersonName,workFlowInfo.PersonPhone);
                dataAccess.RunSql(strSql);

                strSql = string.Format("update st_work_item set exusedtime=0 where iid={0}",
                    workFlowInfo.InstanceId);
                dataAccess.RunSql(strSql);

                strSql = string.Format("update 业务信息明细表 set iid={0} where 业务编号='{1}'",
                    workFlowInfo.InstanceId, workFlowInfo.SerialNo);
                dataAccess.RunSql(strSql);

                strSql = string.Format("insert into ut_物业资质申报意见表(iid,企业名称,Form_name,"+
                    "input_index,resource_name) values({0},'{1}','表格数据/物业资质申报意见表',0,"+
                    "'表格数据/物业资质申报意见表')",workFlowInfo.InstanceId, workFlowInfo.RequestUnit);
                dataAccess.RunSql(strSql);

                dataAccess.Close(true);
            }
            catch
            {
                dataAccess.Close(false);
                throw;
            }
        }
   
  
        /// <summary>
        /// 获得流程超期的方法
        /// </summary>
        /// <param name="nFlagTimeOut">超期的标志</param>
        /// <param name="strWorkFlowId">工作流程id</param>
        /// <param name="strDateStart">开始日期</param>
        /// <param name="strDateEnd">终止日期</param>
        /// <param name="strTypeOfOverFlow">流程办结的标志(1表示办结)</param>
        /// <returns></returns>
        public DataTable GetAllInstanceInfo(int nFlagTimeOut,String strWorkFlowId,String strDateStart,String strDateEnd ,
            String strTypeOfOverFlow)
        {
            String sql = "select a.wid, a.iid, a.name, a.accepted_time,a.ExTotalTime,a.Exusedtime from st_instance a where 1=1 ";

            if (!strWorkFlowId.Equals("1"))//不是查找所有流程的话
            {
                sql +=" and a.wid in (select wid from st_workflow where wname='" + strWorkFlowId + "')";
            }

            if (!string.IsNullOrEmpty(strDateStart) && !string.IsNullOrEmpty(strDateEnd))
            {
                sql += " and a.accepted_time>=to_date('" + strDateStart + "','YYYY-MM-DD HH24:MI:SS') and a.accepted_time<=to_date('" + strDateEnd + "','YYYY-MM-DD HH24:MI:SS')";
            }

            if (strTypeOfOverFlow.Equals("1"))//查找办结的业务
            {
                sql += " and a.iid not in (select distinct iid from st_work_item)";
            }
            else//未办结
            {
                sql += " and a.iid in (select distinct iid from st_work_item)";
            }

            if (nFlagTimeOut == 1)//业务延期
            {
                sql += " and exusedtime>extotaltime";
            }
            else if (nFlagTimeOut == 2)//岗位延期
            {
                sql += " and a.iid in (select distinct iid from st_work_item where exusedtime>extotaltime)";
            }
            else if (nFlagTimeOut == 3)//挂起
            {
                sql += " and a.iid in (select distinct iid from st_suspend)";
            }

            DataTable dtOut;
            SysParams.OAConnection().RunSql(sql, out dtOut);

            return dtOut;
        }

        #region 绩效考核

        /// <summary>
        /// (岗位统计)统计某个岗位上的工作情况。可能是看某个人在几个时间段的工作情况，可能是看某个时间段内，所有相关人的工作情况。
        /// </summary>
        /// <param name="strWorkFlowName">工作流名称</param>
        /// <param name="strStepName">岗位名称</param>
        /// <param name="strTimeStart">开始时间</param>
        /// <param name="strTimeEnd">终止时间</param>
        /// <returns></returns>
        public ArrayList WorkStatusOfStep(String strWorkFlowName, String strStepName,
            String strTimeStart, String strTimeEnd)
        {
            String strCondition = string.Empty;

            if (!string.IsNullOrEmpty(strTimeStart))
            {
                strCondition += string.Format(" and hist.accepted_time>=to_date('{0}','YYYY-MM-DD HH24:MI:SS')",
                    strTimeStart);
            }

            if (!string.IsNullOrEmpty(strTimeEnd))
            {
                strCondition += string.Format(" and hist.accepted_time<=to_date('{0}','YYYY-MM-DD HH24:MI:SS')",
                    strTimeEnd);
            }

            String sql = string.Format("select u.user_name,hist.userid,count(*) as CNT,sum(hist.exusedtime) as" +
                " TOTAL_TIME from st_work_item_hist hist left join st_instance ins on ins.iid = hist.iid" +
                " left join st_user u on u.userid = hist.userid where ins.wid in " +
                "(select wid from st_workflow wf where wf.wname='{0}') " +
                "and hist.step = '{1}' {2} group by hist.userid,u.user_name", strWorkFlowName,
                strStepName, strCondition);

            DataTable dtOut;
            SysParams.OAConnection().RunSql(sql, out dtOut);

            ArrayList alSource = new ArrayList();
            foreach (DataRow drRecord in dtOut.Rows)
            {
                List<string> lstReturn = new List<string>();
                lstReturn.Add(drRecord["user_name"].ToString());
                lstReturn.Add(drRecord["userid"].ToString());
                lstReturn.Add(drRecord["CNT"].ToString());
                lstReturn.Add(drRecord["TOTAL_TIME"].ToString());

                string strStoreSql = string.Format("select ins.* from st_work_item_hist hist " +
                    "left join st_instance ins on ins.iid = hist.iid " +
                    "left join st_user u on u.userid = hist.userid " +
                    "where ins.wid in (select wid from st_workflow wf where wf.wname='{0}') " +
                            "and hist.step = '{1}' and u.userid='{2}' {3}"
                            , strWorkFlowName, strStepName, drRecord["userid"], strCondition);
                lstReturn.Add(strStoreSql);

                alSource.Add(lstReturn);
            }

            return alSource;
        }


        /// <summary>
        /// (部门统计)获取某个部门的所有人，在某个流程的所有岗位上的执行情况。时间的范围为案例的接收时间。
        /// </summary>
        /// <param name="strWorkFlowName">流程名</param>
        /// <param name="strDepartmentId">部门id</param>
        /// <param name="strTimeStart">接件的开始时间</param>
        /// <param name="strTimeEnd">接件的终止时间</param>
        /// <param name="strOrderBy">排序的方式</param>
        /// <returns></returns>
        public ArrayList WorkStatusOfStepAndDepartment(String strWorkFlowName, String strDepartmentId,
            String strTimeStart, String strTimeEnd, string strOrderBy)
        {
            String strCondition = string.Empty;

            if (!string.IsNullOrEmpty(strTimeStart))
            {
                strCondition += string.Format(" and hist.accepted_time>=to_date('{0}','YYYY-MM-DD HH24:MI:SS')",
                    strTimeStart);
            }

            if (!string.IsNullOrEmpty(strTimeEnd))
            {
                strCondition += string.Format(" and hist.accepted_time<=to_date('{0}','YYYY-MM-DD HH24:MI:SS')",
                    strTimeEnd);
            }

            String sql = string.Format("select u.user_name,hist.userid,hist.step,count(*) as CNT," +
                "sum(hist.exusedtime) as TOTAL_TIME from st_work_item_hist hist left join " +
                "st_instance ins on ins.iid = hist.iid " +
                " left join st_user u on u.userid = hist.userid where ins.wid in " +
                "(select wid from st_workflow wf where wf.wname='{0}') " +
                "and hist.userid in (select userid from st_user_department where order_id= '{1}') {2} " +
                "group by hist.userid,u.user_name,hist.step order by {3}", strWorkFlowName,
                strDepartmentId, strCondition, strOrderBy);

            DataTable dtOut;
            SysParams.OAConnection().RunSql(sql, out dtOut);

            ArrayList alSource = new ArrayList();
            foreach (DataRow drRecord in dtOut.Rows)
            {
                Dictionary<string, string> dicReturn = new Dictionary<string, string>();

                dicReturn.Add("USER_NAME", drRecord["user_name"].ToString());
                dicReturn.Add("USERID", drRecord["userid"].ToString());
                dicReturn.Add("CNT", drRecord["CNT"].ToString());
                dicReturn.Add("TOTAL_TIME", drRecord["TOTAL_TIME"].ToString());

                string strStoreSql = string.Format("select ins.* from st_work_item_hist hist " +
                    "left join st_instance ins on ins.iid = hist.iid " +
                    "left join st_user u on u.userid = hist.userid " +
                    "where ins.wid in (select wid from st_workflow wf where wf.wname='{0}') " +
                            "and hist.step = '{1}' and u.userid='{2}' {3}"
                            , strWorkFlowName, drRecord["step"], drRecord["userid"], strCondition);
                dicReturn.Add("detailSql", strStoreSql);

                alSource.Add(dicReturn);
            }

            return alSource;
        }

        /// <summary>
        /// 违规统计
        /// </summary>
        /// <param name="strAllUsers">所有用户信息</param>
        /// <param name="strTimeStart">开始时间</param>
        /// <param name="strTimeEnd">终止时间</param>
        /// <param name="strOrderBy">排序方式</param>
        /// <returns></returns>
        public ArrayList StaticOfInspect(String strAllUsers,String strTimeStart, String strTimeEnd,
            string strOrderBy)
        {
            if (string.IsNullOrEmpty(strOrderBy))
            {
                strOrderBy = "u.user_name";
            }

            String strCondition = string.Empty;

            if (!string.IsNullOrEmpty(strTimeStart))
            {
                strCondition += string.Format(" and inspect.违规时间>=to_date('{0}','YYYY-MM-DD HH24:MI:SS')",
                    strTimeStart);
            }

            if (!string.IsNullOrEmpty(strTimeEnd))
            {
                strCondition += string.Format(" and inspect.违规时间<=to_date('{0}','YYYY-MM-DD HH24:MI:SS')",
                    strTimeEnd);
            }

            string strUserCondition = string.Empty;
            if (!string.IsNullOrEmpty( strAllUsers ))
            {
                strUserCondition = string.Format(" and u.userid in ({0})",strAllUsers);
            }

            String sql = string.Format("select wf.wname,u.user_name,u.userid from xt_inspect inspect " +
				"left join st_instance ins on inspect.iid = ins.iid " +
				"left join st_workflow wf on ins.wid = wf.wid " +
				"left join st_user u on inspect.被告人=u.userid " +
				"where inspect.是否成立=1 {0} {1} group by wf.wname,u.user_name,u.userid order by {2}",
                strCondition,strUserCondition,strOrderBy);

            DataTable dtOut;
            SysParams.OAConnection().RunSql(sql, out dtOut);

            ArrayList alSource = new ArrayList();
            foreach (DataRow drRecord in dtOut.Rows)
            {
                Dictionary<string, string> dicReturn = new Dictionary<string, string>();

                string strTempUserId = drRecord["userid"].ToString();
                string strTempWorkName = drRecord["WNAME"].ToString();
                dicReturn.Add("WNAME", strTempWorkName);
                dicReturn.Add("USERID", strTempUserId);

                String sql1 = string.Format("select count(*) as CNT from xt_inspect inspect" +
                    " left join st_instance ins on inspect.iid = ins.iid" +
                    " left join st_workflow wf on ins.wid = wf.wid" +
                    " where inspect.是否成立=1  and inspect.事件类型 = 1" +
                    " and inspect.被告人='{0}' and wf.wname = '{1}' {2}",
                    strTempUserId, strTempWorkName, strCondition);
                string strCnt1 = SysParams.OAConnection().GetValue(sql1); 

                String sql2 = string.Format("select count(*) as CNT from xt_inspect inspect" +
                    " left join st_instance ins on inspect.iid = ins.iid" +
                    " left join st_workflow wf on ins.wid = wf.wid" +
                    " where inspect.是否成立=1 and inspect.事件类型 = 2" +
                    " and inspect.被告人='{0}' and wf.wname = '{1}' {2}",
                    strTempUserId, strTempWorkName, strCondition);
                string strCnt2 = SysParams.OAConnection().GetValue(sql2);

                dicReturn.Add("type1", strCnt1);
                dicReturn.Add("type2", strCnt2);

                alSource.Add(dicReturn);
            }

            return alSource;
        }

        /// <summary>
        /// (个人统计)
        /// </summary>
        /// <param name="strWorkFlowName">流程名</param>
        /// <param name="strStepName">岗位名称</param>
        /// <param name="strUserId">用户id</param>
        /// <param name="strTimeStart">接件起始时间</param>
        /// <param name="strTimeEnd">接件结束时间</param>
        /// <param name="strIntervalType">获取接收日期的类型（年、月、日）</param>
        /// <returns></returns>
        public ArrayList WorkStatusOfPersonalStep(String strWorkFlowName, String strStepName,
            String strUserId,String strTimeStart, String strTimeEnd, string strIntervalType)
        {
            String strCondition = string.Empty;

            if (!string.IsNullOrEmpty(strTimeStart))
            {
                strCondition += string.Format(" and hist.accepted_time>=to_date('{0}','YYYY-MM-DD HH24:MI:SS')",
                    strTimeStart);
            }

            if (!string.IsNullOrEmpty(strTimeEnd))
            {
                strCondition += string.Format(" and hist.accepted_time<=to_date('{0}','YYYY-MM-DD HH24:MI:SS')",
                    strTimeEnd);
            }

            String intervalCondition = string.Empty;
            String intervalGroupByCondition = string.Empty;

            if (!string.IsNullOrEmpty(strIntervalType))
            {
                if (strIntervalType.Equals("day"))
                {
                    intervalCondition += " extract(year from hist.accepted_time) as year,extract(month from hist.accepted_time) month, extract(day from hist.accepted_time) as day ";
                    intervalGroupByCondition += " extract(year from hist.accepted_time),extract(month from hist.accepted_time),extract(day from hist.accepted_time) ";
                }
                else if (strIntervalType.Equals("month"))
                {
                    intervalCondition += " extract(year from hist.accepted_time) as year,extract(month from hist.accepted_time) month ";
                    intervalGroupByCondition += " extract(year from hist.accepted_time),extract(month from hist.accepted_time) ";
                }
                else if (strIntervalType.Equals("year"))
                {
                    intervalCondition += " extract(year from hist.accepted_time) as year ";
                    intervalGroupByCondition += " extract(year from hist.accepted_time) ";
                }

            }

            String sql = string.Format("select {0},count(*) as CNT,sum(hist.exusedtime) as TOTAL_TIME"+
                "from st_work_item_hist hist left join st_instance ins on ins.iid = hist.iid" +
                " where ins.wid in (select wid from st_workflow wf where wf.wname='{1}') " +
                    " and hist.step='{2}' and hist.userid='{3}' {4}  group by {5}",
                    intervalCondition, strWorkFlowName, strStepName, strUserId, strCondition,
                    intervalGroupByCondition);

            DataTable dtOut;
            SysParams.OAConnection().RunSql(sql, out dtOut);

            ArrayList alSource = new ArrayList();
            foreach (DataRow drRecord in dtOut.Rows)
            {
                Dictionary<string, string> dicReturn = new Dictionary<string, string>();

                String year = drRecord["day"].ToString();
                String month = drRecord["month"].ToString();
                String day = drRecord["year"].ToString();

                dicReturn.Add("day", drRecord["day"].ToString());
                dicReturn.Add("month", drRecord["month"].ToString());
                dicReturn.Add("year", drRecord["year"].ToString());
                dicReturn.Add("CNT", drRecord["CNT"].ToString());
                dicReturn.Add("TOTAL_TIME", drRecord["TOTAL_TIME"].ToString());

                String strStoreSql = "select hist.iid from st_work_item_hist hist " +
                     "left join st_instance ins on hist.iid=ins.iid " +
                     " where 1=1 ";
                String strTimeCondition = string.Empty;//时间范围
                if (strIntervalType.Equals("day"))
                {
                    strTimeCondition += " and extract(year from hist.accepted_time)='" + year + "'";
                    strTimeCondition += " and extract(month from hist.accepted_time)='" + month + "'";
                    strTimeCondition += " and extract(day from hist.accepted_time)='" + day + "'";
                }
                else if (strIntervalType.Equals("month"))
                {
                    strTimeCondition += " and extract(year from hist.accepted_time)='" + year + "'";
                    strTimeCondition += " and extract(month from hist.accepted_time)='" + month + "'";
                }
                else if (strIntervalType.Equals("year"))
                {
                    strTimeCondition += " and extract(year from hist.accepted_time)='" + year + "'";
                }
                strStoreSql += strTimeCondition;
                strStoreSql += " and ins.wid in " +
                " (select wid from st_workflow wf where wf.wname='" + strWorkFlowName + "') " +
                " and hist.step='" + strStepName + "' and hist.userid='" + strUserId + "'";
                strStoreSql += " group by hist.iid";
                strStoreSql = "select * from st_instance where iid in (" + strStoreSql + ")";

                dicReturn.Add("detailSql", strStoreSql);

                alSource.Add(dicReturn);
            }

            return alSource;
        } 

        /// <summary>
        /// 对案件进行统计。某个流程或全部流程，指定时间内或全部时间内，案件处理情况在各部门之间的分布
        /// </summary>
        /// <param name="strWorkFlowName">工作流名称</param>
        /// <param name="strTimeStart">起始时间</param>
        /// <param name="strTimeEnd">终止时间</param>
        /// <returns></returns>
        public Dictionary<string, string> StaticOfInstance(String strWorkFlowName, 
            String strTimeStart, String strTimeEnd)
        {
		    String strCondition = string.Empty;

		    if (!string.IsNullOrEmpty(strTimeStart))
            {
                strCondition += string.Format(" and ins.accepted_time>=to_date('{0}','YYYY-MM-DD HH24:MI:SS')",
                    strTimeStart);
            }

            if (!string.IsNullOrEmpty(strTimeEnd))
            {
                strCondition += string.Format(" and ins.accepted_time<=to_date('{0}','YYYY-MM-DD HH24:MI:SS')",
                    strTimeEnd);
            }
		
		
            string strOtherSql = string.Empty;
            if( !string.IsNullOrEmpty( strWorkFlowName))
            {
                strOtherSql = " and ins.wid in (select wid from st_workflow wf where wf.wname='"+strWorkFlowName+"') ";
            }

		    String sql = "select wf.wname,wf.wid,ins.*  from st_instance ins left join st_workflow "+
                "wf on wf.wid = ins.wid where 1=1 "+strOtherSql+strCondition+" order by wf.wname";

            DataTable dtOut;
            SysParams.OAConnection().RunSql(sql,out dtOut);

            //设置sql
		    int status0 = 0;
		    int status1 = 0;
		    int status2 = 0;
		    int exusedtimeTotal = 0;
		    int instanceCount = 0;
		    int alarm = 0;
		    int overtime = 0;
		    int priority0 = 0;
		    int priority1 = 0;
		    int priority2 = 0;

            string detailSql = "select * from st_instance where iid in (";

            Dictionary<string, string> lstReturn = new Dictionary<string, string>();

            foreach (DataRow drRecord in dtOut.Rows)
            {
                String status = drRecord["STATUS"].ToString();
                String priority = drRecord["PRIORITY"].ToString();

                int nExtotalTime = 0;
                int nExUsedTime = 0;
                int nExalarmTime = 0;
                if (!string.IsNullOrEmpty(drRecord["EXTOTALTIME"].ToString()))
                {
                    nExtotalTime = Convert.ToInt32(drRecord["EXTOTALTIME"].ToString());
                }
                if (!string.IsNullOrEmpty(drRecord["EXUSEDTIME"].ToString()))
                {
                    if (!drRecord["EXUSEDTIME"].ToString().Equals("0"))
                    {
                        nExUsedTime = Convert.ToInt32(string.Format("{0:#}", drRecord["EXUSEDTIME"]));
                    }
                }
                if (!string.IsNullOrEmpty(drRecord["EXALARMTIME"].ToString()))
                {
                    nExalarmTime = Convert.ToInt32(drRecord["EXALARMTIME"].ToString());
                }

                if (!string.IsNullOrEmpty(drRecord["IID"].ToString()))
                {
                    if (instanceCount != 0)
                    {
                        detailSql += ",";
                    }

                    detailSql += drRecord["IID"].ToString();
                }

			    if( status.Equals("0"))
                    status0++;
                if (status.Equals("1"))
                    status1++;
                if (status.Equals("2"))
                    status2++;

                exusedtimeTotal += nExtotalTime;
			    instanceCount++;

                if (priority.Equals("0"))
                    priority0++;
                if (priority.Equals("1"))
                    priority1++;
                if (priority.Equals("2"))
                    priority2++;
    			
			    if(nExtotalTime != 0)
                {
				    if(nExUsedTime > nExtotalTime)
                    {
                        overtime++;
				    }
                    else if(nExUsedTime > nExalarmTime)
                    {
					    alarm++;
				    }
			    }
            }//for end

            //统计记录
            lstReturn.Add("status0", status0.ToString());
            lstReturn.Add("status1", status1.ToString());
            lstReturn.Add("status2", status2.ToString());
            lstReturn.Add("exusedtimeTotal", exusedtimeTotal.ToString());
            lstReturn.Add("instanceCount", instanceCount.ToString());

            lstReturn.Add("alarm", alarm.ToString());
            lstReturn.Add("overtime", overtime.ToString());
            lstReturn.Add("priority0", priority0.ToString());
            lstReturn.Add("priority1", priority1.ToString());
            lstReturn.Add("priority2", priority2.ToString());

            detailSql += ")";
            lstReturn.Add("detailSql", detailSql);

            return lstReturn;
        }

        /// <summary>
        /// 对案件进行统计。某个流程或全部流程，指定时间内或全部时间内，案件处理情况在各部门之间的分布
        /// </summary>
        /// <param name="strWorkFlowName">工作流名称</param>
        /// <param name="strOrderId">管理的组id</param>
        /// <param name="strTimeStart">开始时间</param>
        /// <param name="strTimeEnd">终止日期</param>
        /// <returns></returns>
        public Dictionary<string, string> StaticOfInstance(String strWorkFlowName, String strOrderId, 
            String strTimeStart, String strTimeEnd)
        {
            String strCondition = string.Empty;

            if (!string.IsNullOrEmpty(strTimeStart))
            {
                strCondition += string.Format(" and hist.accepted_time>=to_date('{0}','YYYY-MM-DD HH24:MI:SS')",
                    strTimeStart);
            }

            if (!string.IsNullOrEmpty(strTimeEnd))
            {
                strCondition += string.Format(" and hist.accepted_time<=to_date('{0}','YYYY-MM-DD HH24:MI:SS')",
                    strTimeEnd);
            }

            string strWorkFlowCondition = string.Empty;
            if (!string.IsNullOrEmpty(strWorkFlowName))
            {
                strWorkFlowCondition = string.Format(" and ins.wid in (select wid from st_workflow wf" +
                    " where wf.wname='{0}')", strWorkFlowName);
            }

            string strOrderCondition = string.Empty;
            if (!string.IsNullOrEmpty(strOrderId))
            {
                strOrderCondition = string.Format(" and ( hist.userid in (select userid from st_user_department " +
                    "where order_id like '{0}%') or item.userid in (select userid from st_user_department " +
                    "where order_id like '{0}%')) and dep.order_id like '{0}%'", strOrderId);
            }

            //需要同时考虑刚开始办的，即hist表里没有的，也要考虑已经办结的，即item表里没有的
            //现在使用的是like，所以统计包括所选部门，以及下属部门，这不是说将下属部门的案件计入上属部门
            //如果一个案件经手多个部门的话，上面的条件不能过滤掉其他部门，或许sql不应该现在这样设计
            //ins.iid,ins.status一一对应，iid是ins的主键所以有了ins.iid后案例表中的所有字段都可以放到group by里

            String sql = string.Format("select dep.departid,dep.order_id,dep.depart_name,ins.iid,ins.status," +
                "ins.exusedtime,ins.PRIORITY,ins.EXTOTALTIME,ins.EXUSEDTIME,ins.EXALARMTIME from st_instance ins" +
                " left join st_work_item_hist hist on hist.iid = ins.iid" +
                " left join st_work_item item on item.iid = ins.iid" +
                " left join st_user u on (u.userid = hist.userid or u.userid = item.userid)" +
                " left join st_user_department uxd on uxd.userid=u.userid" +
                " left join st_department dep on dep.order_id = uxd.order_id where 1=1 {0} {1}" +
                " group by dep.departid,dep.order_id,dep.depart_name,ins.iid,ins.status,ins.exusedtime," +
                "ins.PRIORITY,ins.EXTOTALTIME,ins.EXUSEDTIME,ins.EXALARMTIME" +
                " order by dep.order_id", strWorkFlowCondition, strOrderCondition);

            DataTable dtOut;
            SysParams.OAConnection().RunSql(sql, out dtOut);

            int status0 = 0;
            int status1 = 0;
            int status2 = 0;
            int exusedtimeTotal = 0;
            int instanceCount = 0;
            int alarm = 0;
            int overtime = 0;
            int priority0 = 0;
            int priority1 = 0;
            int priority2 = 0;

            String detailSql = "select * from st_instance where iid in (";
            Dictionary<string, string> lstReturn = new Dictionary<string, string>();

            foreach (DataRow drRecord in dtOut.Rows)
            {
                String strOrder = drRecord["ORDER_ID"].ToString();

                if (string.IsNullOrEmpty(strOrder))
                {
                    continue;
                }

                String status = drRecord["STATUS"].ToString();
                String priority = drRecord["PRIORITY"].ToString();

                int nExtotalTime = 0;
                int nExUsedTime = 0;
                int nExalarmTime = 0;
                if (!string.IsNullOrEmpty(drRecord["EXTOTALTIME"].ToString()))
                {
                    nExtotalTime = Convert.ToInt32(drRecord["EXTOTALTIME"].ToString());
                }
                if (!string.IsNullOrEmpty(drRecord["EXUSEDTIME"].ToString()))
                {
                    if (!drRecord["EXUSEDTIME"].ToString().Equals("0"))
                    {
                        nExUsedTime = Convert.ToInt32(string.Format("{0:#}", drRecord["EXUSEDTIME"]));
                    }
                }
                if (!string.IsNullOrEmpty(drRecord["EXALARMTIME"].ToString()))
                {
                    nExalarmTime = Convert.ToInt32(drRecord["EXALARMTIME"].ToString());
                }

                if (!string.IsNullOrEmpty(drRecord["IID"].ToString()))
                {
                    if (instanceCount != 0)
                    {
                        detailSql += ",";
                    }

                    detailSql += drRecord["IID"].ToString();
                }

                if (status.Equals("0"))
                    status0++;
                if (status.Equals("1"))
                    status1++;
                if (status.Equals("2"))
                    status2++;

                exusedtimeTotal += nExtotalTime;
                instanceCount++;

                if (priority.Equals("0"))
                    priority0++;
                if (priority.Equals("1"))
                    priority1++;
                if (priority.Equals("2"))
                    priority2++;

                if (nExtotalTime != 0)
                {
                    if (nExUsedTime > nExtotalTime)
                    {
                        overtime++;
                    }
                    else if (nExUsedTime > nExalarmTime)
                    {
                        alarm++;
                    }
                }
            }

            //统计记录
            lstReturn.Add("status0", status0.ToString());
            lstReturn.Add("status1", status1.ToString());
            lstReturn.Add("status2", status2.ToString());
            lstReturn.Add("exusedtimeTotal", exusedtimeTotal.ToString());
            lstReturn.Add("instanceCount", instanceCount.ToString());

            lstReturn.Add("alarm", alarm.ToString());
            lstReturn.Add("overtime", overtime.ToString());
            lstReturn.Add("priority0", priority0.ToString());
            lstReturn.Add("priority1", priority1.ToString());
            lstReturn.Add("priority2", priority2.ToString());

            detailSql += ")";
            lstReturn.Add("detailSql", detailSql);

            return lstReturn;
        }
	

        #endregion
    }
}
